#!/usr/bin/env python

# dictionary - month names
Month = {}
Month['Jan'] = '01'
Month['Feb'] = '02'
Month['Mar'] = '03'
Month['Apr'] = '04'
Month['May'] = '05'
Month['Jun'] = '06'
Month['Jul'] = '07'
Month['Aug'] = '08'
Month['Sep'] = '09'
Month['Oct'] = '10'
Month['Nov'] = '11'
Month['Dec'] = '12'

# input DD mon YY
# output YYYY-MM-DD
def format_date(arg):
    [DD,Mon,YY] = arg.split()
    # print YY,Month[Mon],DD
    if ( int(YY) <= 20 ):
        YYYY = '20' + YY
    else:
        YYYY = '19' + YY
    return YYYY + '-' + Month[Mon] + '-' + DD

import sys
import MySQLdb
# reading a CSV file
import csv

print len(sys.argv),' args'

if len(sys.argv) > 1:
    filename = sys.argv[1]
else:
    print 'Usage: ',sys.argv[0],' filename'
    sys.exit(1)

#DB name
DBName = 'BankOfEngland'
# Table name
tname = 'IRDB_TEST'

# Open database connection
DB_UserID='gerald'
DB = MySQLdb.connect('localhost',DB_UserID,'My?DB!',DBName)
cursor = DB.cursor()

# get a list of tables
print '\nExisting Tables:'
cursor.execute('SHOW TABLES')
Tables = []
for tbl in cursor.fetchall():
    Tables.append(tbl[0])
    print tbl[0]
print '\n'

# what dates in the table?
Dates=[]
if ( tname in Tables ):
    sql = 'SELECT DATE FROM ' + tname
    cursor.execute(sql)
    for row in cursor.fetchall():
        Dates.append(str(row[0]))

count=0
exist=0
# The first line contains the maturities
first = True
for line in open(filename,'r'):
    if ( ',,,,,' in line ):
        continue
    vars = line.rstrip().split(',')
    # print vars[0],len(vars)
    # setup table columns
    if ( first ):
        first = False
        Maturity = vars[1:]
        column = []
        # It doesn't seem to like periods in column names
        for mat in Maturity:
            column.append(mat.replace('.','_') + 'YR')
        # SQL to create table
        sql = 'CREATE TABLE IF NOT EXISTS ' + tname + '('
        # setup parameterized SQL for insert
        sql_param = 'INSERT INTO ' + tname + ' (DATE'
        val_param = ' VALUES (%s'
        sql += 'DATE DATE NOT NULL PRIMARY KEY'
        # IR data by maturity
        for col in column:
            sql += ',' + col + ' FLOAT'
            sql_param += ',' + col
            val_param += ',%s'
        sql += ');'
        val_param += ')'
        sql_param += ')' + val_param
        # create the table if it doesn't exist
        print 'SQL: ',sql
        cursor.execute(sql)
        #print 'SQL INSERT param'
        #print sql_param
    else:
        date = format_date(vars[0])
        if ( date in Dates ):
            # print date,' exists'
            exist += 1
            continue
        # prameterized SQL
        param = [date] + vars[1:]
        # print param
        cursor.execute(sql_param,param)
        count += 1

# commit the changes
DB.commit()
DB.close()

print exist,' existing records'
print count,' records added'
sys.exit(0)

# get a list of tables
# cursor.execute('SHOW TABLES')
# tables = cursor.fetchall()
# creates a tuple
# for table in tables:
#     print "Tables: %s " % table
#     sql = 'DESCRIBE ' + table[0]
#     print 'SQL = ',sql
#     cursor.execute(sql)
#     results = cursor.fetchall()
#     for row in results:
#         print row
#result = cursor.fetchall()
#print result
